﻿using System;
using System.Collections.Generic; 
using System.Text;
using System.Data;
using Microsoft.JCI.Common.Utility; 
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace Microsoft.JCI.Dal
{
    public partial class DisWorkingVendorOrderDal : DalBase
    {
        public DataTable QueryWorkItems(string WhereClause, int PageSize, int PageIndex, ref int RecordCount)
        {
            DataTable dt = new DataTable();
//            string sql = @"select * from (
//select A.*,B.FormCode,b.AllocatedVendor,b.AllocatedBranch,c.Name from Jci_Dis_WorkingVendorOrder A join Jci_Dis_DispatchForm B
//on A.DispatchFormId = B.DispatchFormId
//join Jci_Mad_Customer C on A.CustomerId = c.CustomerId
//) as A where 1=1";
            string sql = @"select * from (select A.*,case A.IsChecked when 1 then '已审核' else '未审核' end as IsCheck,B.FormCode,b.AllocatedVendor,b.AllocatedBranch,c.[Name],c.Address,D.[Name] as BranchName,E.[Name] as VenderName from Jci_Dis_WorkingVendorOrder A join Jci_Dis_DispatchForm B on A.DispatchFormId = B.DispatchFormId join Jci_Mad_Customer C on A.CustomerId = c.CustomerId 
left join Jci_Sys_Company D on b.AllocatedBranch=D.CompanyCode left join Jci_Sys_Company E on b.AllocatedVendor=E.CompanyCode) as A where 1=1";

            sql += WhereClause;

            string CmdText = @"WITH t AS
(
SELECT ROW_NUMBER() OVER(ORDER BY CreateOn Desc) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from t WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum";

            CmdText = string.Format(CmdText, sql.ToString());

            System.Data.Common.DbCommand dbCommand = CurrentDatabase.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            dt = CurrentDatabase.ExecuteDataSet(dbCommand).Tables[0];

            ////////////////
            CmdText = @"SELECT  COUNT(*) FROM  ({0}) as B";
            CmdText = string.Format(CmdText, sql.ToString());
            dbCommand = CurrentDatabase.GetSqlStringCommand(CmdText);

            RecordCount = (int)this.CurrentDatabase.ExecuteScalar(dbCommand);
            ////////////////
            return dt;
        } 
    }
}
